package com.zsrt.bbu.card.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.zsrt.bbu.card.dao.CardDao;
import com.zsrt.bbu.card.entity.CardEntity;
import com.zsrt.utils.DBUtil;

public class CardDaoImpl implements CardDao {

	@Override
	public int add(CardEntity entity) throws Exception {

		int counter = 0;
		// 1 加载驱动并拿到连接 成功
		Connection connection = DBUtil.getConnection();

		String sql = "insert into `bbu_card_t`(CARD_NO,CHIP,BALANCE,OWNER,BANK_ID) values(?,?,?,?,?)";

		PreparedStatement ps = connection.prepareStatement(sql);

		ps.setString(1, entity.getCardNo());
		ps.setString(2, entity.getChip());
		ps.setDouble(3, entity.getBalance());
		ps.setString(4, entity.getOwner());
		ps.setInt(5, entity.getBankId());

		counter = ps.executeUpdate();

		DBUtil.closeResources(ps, connection);

		return counter;
	}

	@Override
	public List<CardEntity> findAllCards() throws Exception {
		List<CardEntity> list = new ArrayList<CardEntity>();

		String sql = " SELECT card.id as id , "
				+ " case card.chip when 1 then '有' else '无' end as chipName , "
				+ " card.CARD_NO as cardNo, card.CHIP as chip,card.BALANCE as balance, card.`OWNER` as owner , card.BANK_ID as bankId,bank.BANK_NAME as bankName "
				+ " FROM `bbu_card_t` card " + " INNER JOIN  bbu_bank_t bank  on bank.ID= card.BANK_ID order by card.id desc ";
		Connection connection = DBUtil.getConnection();
		PreparedStatement ps = connection.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();

		while (rs.next()) {
			CardEntity card = new CardEntity();
			card.setChipName(rs.getString("chipName"));
			card.setId(rs.getInt("id"));
			card.setCardNo(rs.getString("cardNo"));
			card.setChip(rs.getString("chip"));
			card.setBalance(rs.getDouble("balance"));
			card.setBankId(rs.getInt("bankId"));
			card.setBankName(rs.getString("bankName"));
			card.setOwner(rs.getString("owner"));

			list.add(card);

		}
		DBUtil.closeResources(rs, ps, connection);
		return list;
	}

	@Override
	public int update(CardEntity entity) throws Exception {

		// 1 加载驱动并拿到连接 成功
		int counter = 0;
		Connection connection = DBUtil.getConnection();
		String sql = "update bbu_card_t card  "
					+ " set  "
						+ " CARD_NO = ?, "
						+ " CHIP = ?, "
						+ " BALANCE = ?, "
						+ " `OWNER` = ?, "
						+ " BANK_ID  = ? "
					+ " where ID = ?";
		PreparedStatement ps = connection.prepareStatement(sql);

		ps.setString(1, entity.getCardNo());
		ps.setString(2, entity.getChip());
		ps.setDouble(3, entity.getBalance());
		ps.setString(4, entity.getOwner());
		ps.setInt(5, entity.getBankId());
		ps.setInt(6, entity.getId());
		
		
		counter = ps.executeUpdate();

		DBUtil.closeResources(ps, connection);
		return counter;
	}

	@Override
	public CardEntity findById(Integer id) throws Exception {
		CardEntity card = new CardEntity();

		String sql = "SELECT card.id as id , card.CARD_NO as cardNo, card.CHIP as chip,card.BALANCE as balance, card.`OWNER` as owner , card.BANK_ID as bankId FROM `bbu_card_t` card where id=?";
		Connection connection = DBUtil.getConnection();
		PreparedStatement ps = connection.prepareStatement(sql);
		
		ps.setInt(1,id);
		ResultSet rs = ps.executeQuery();

		if (rs.next()) {
			card.setId(rs.getInt("id"));
			card.setCardNo(rs.getString("cardNo"));
			card.setChip(rs.getString("chip"));
			card.setBalance(rs.getDouble("balance"));
			card.setBankId(rs.getInt("bankId"));
			card.setOwner(rs.getString("owner"));
		}
		DBUtil.closeResources(rs, ps, connection);
		return card;
	}

}
